package business.search_eng.cleaner;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.aliasi.tokenizer.Tokenizer;
import com.aliasi.tokenizer.TokenizerFactory;
import com.aliasi.tokenizer.LowerCaseTokenizerFactory;
import com.aliasi.tokenizer.EnglishStopTokenizerFactory;
import com.aliasi.tokenizer.IndoEuropeanTokenizerFactory;


import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

public class EnglishStopWordsRemover {
	
	public EnglishStopWordsRemover() {
	}
	
	public String RemoveStopWords (String aSentence) {
		String[] tokenizedSentence = this.removeStopWords(aSentence);
		StringBuilder sb = new StringBuilder();
		for(int i = 0; i < tokenizedSentence.length; i++) {
			sb.append(tokenizedSentence[i] + " ");
		}
		sb.append("\n");
		return sb.toString();
	}
	
	public String[] removeStopWords (String aSentence) {
		char[] phraseToTokenize = aSentence.toCharArray();
		TokenizerFactory indoEuropeanTokenizerFactory = IndoEuropeanTokenizerFactory.INSTANCE;
		LowerCaseTokenizerFactory lowerCaseTokenizerFactory = new LowerCaseTokenizerFactory(indoEuropeanTokenizerFactory);
		EnglishStopTokenizerFactory englishStopWordTokenizer = new EnglishStopTokenizerFactory(lowerCaseTokenizerFactory);
		Tokenizer finalTokenizer = englishStopWordTokenizer.tokenizer(phraseToTokenize, 0, phraseToTokenize.length);
		return finalTokenizer.tokenize();
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		EnglishStopWordsRemover stopWordsRemover = new EnglishStopWordsRemover();
		Statement stmt = null;
		ResultSet srs = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/Zasca?" +
			"user=zasca&password=zasca2009");
			
			stmt = (Statement) conn.createStatement();//ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			srs = (ResultSet) stmt.executeQuery("SELECT id, title, subtitle, content FROM Zasca.news_rawtext");
			
			//do select & cross all resultset (now files) 
			int i=0;
	        while (srs.next()) {
				//retrieve all fields
		        int id = srs.getInt("id");
		        if(id == 11920) {
		        String title = srs.getString("title");
		        String subtitle = srs.getString("subtitle");
		        String content = srs.getString("content");
		        
		        String treatedTitle = stopWordsRemover.RemoveStopWords(title).replace("'", "\\'");
		        String treatedSubtitle = stopWordsRemover.RemoveStopWords(subtitle).replace("'", "\\'");
		        String treatedContent = stopWordsRemover.RemoveStopWords(content).replace("'", "\\'");
		        
		        //prepapre the inser
				Statement insertStmt = null;
				try {
					insertStmt = (Statement) conn.createStatement();
					String insert = "insert into news_swtext(id,title,subtitle,content) values ("+id+"," +
							"'"+treatedTitle+"'," +
							"'"+treatedSubtitle+"'," +
							"'"+treatedContent+"')";
					
					if(false)
						System.out.println("\nINSERT:\n"+insert);
					insertStmt.executeUpdate(insert);
					
				} catch (SQLException ex){
				    // handle any errors
				    System.out.println("SQLException: " + ex.getMessage());
				    System.out.println("SQLState: " + ex.getSQLState());
				    System.out.println("VendorError: " + ex.getErrorCode());
				}
				if(i%100 == 0)
					System.out.println(Math.round(i*0.006));
				i++;
	        	}
			}
			
			conn.close();
			
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
		    // it is a good idea to release resources in a finally{} block
		    // in reverse-order of their creation if they are no-longer needed
		    if (srs != null) {
		        try {
		            srs.close();
		        } catch (SQLException sqlEx) { } // ignore

		        srs = null;
		    }
		    if (stmt != null) {
		        try {
		            stmt.close();
		        } catch (SQLException sqlEx) { } // ignore

		        stmt = null;
		    }
		}
	}
	
}

